
* Table 48 
* correlation between H and wealth 

qui{
clear 
use "${data}/HRS_Analysis.dta"
*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 


* keep Whites
drop if hispanic==1 // drop Hispanics
drop if race==2 // drop Blacks

rename iwendmr month // interview end date month

replace total_wealth=total_wealth/1000
replace total_wealth=0 if total_wealth<0
replace total_wealth=. if total_wealth>2000 // ignore those with total wealth over 2 mill

* financial wealth (net value of non-housing wealth + IRAs)
gen total_financial_wealth=atotfh+ airah // net value of non-housing financial wealth - excludes any real estate, vehicles or businesses
replace total_financial_wealth =total_financial_wealth /1000 
replace total_financial_wealth=0 if total_financial_wealth<0
replace total_financial_wealth=. if total_financial_wealth>2000 // ignore those with financial wealth over 2 mill


replace itoth=itoth/1000

* earnings and capital income
gen HHearnings = iearnr
replace HHearnings=HHearnings + iearns if iearns!=.
gen HH_earnings_plus_k_income=HHearnings + icaph
gen earnings_plus_k_income = iearnr+ icaph


gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women

sort year

* variable equivalent to PHYLIMS in MEPS. This is the number of ADL's the respondent has difficulty with
// bigger score is worse
gen PHYLIMS=0 if ADL_combo_diff==0
replace PHYLIMS=1 if ADL_combo_diff>0 & ADL_combo_diff<10
replace PHYLIMS=2 if ADL_combo_diff>9 & ADL_combo_diff<100
replace PHYLIMS=3 if ADL_combo_diff>99 & ADL_combo_diff<1000
replace PHYLIMS=4 if ADL_combo_diff>999 & ADL_combo_diff<10000
replace PHYLIMS=5 if ADL_combo_diff>9999 & ADL_combo_diff<100000
replace PHYLIMS=6 if ADL_combo_diff>99999 & ADL_combo_diff<1000000
replace PHYLIMS=7 if ADL_combo_diff>999999 & ADL_combo_diff<10000000
rename PHYLIMS PHYLIMS1

* self reported health, same as RTHLTH1 in meps
rename health_ORIG RTHLTH1
tab RTHLTH1 // bigger score is worse
* this summary score captures mental health similar to MNHLTH1
rename health_mental MNHLTH1
tab MNHLTH1 // bigger score is worse

* indicator for getting help with an ADL
gen ADLHLP1 = 0 if ADL_combo_help==0 
replace ADLHLP1 = 1 if ADL_combo_help>0 & ADL_combo_help!=.

* same IADL
gen IADLHP1 = 0 if IADL_combo==0 
replace IADLHP1 =1  if IADL_combo>0 & IADL_combo!=.

drop if age<50

********************************************************************************
*** CONSTRUCTING VARIABLES: HEALTH CAPITAL (H) *********************************
********************************************************************************
* standardize based on distribution for ages 54 to 60

	// Standardize the variables used
	qui sum RTHLTH1 if age>53 & age<61 [aweight=wtcrnhr]
	scalar depmean = r(mean)
	scalar depsdev = r(sd)
	gen RTHLTH1_std = ( RTHLTH1 - depmean ) / depsdev


	qui sum MNHLTH1 if age>53 & age<61 [aweight=wtcrnhr]
	scalar depmean = r(mean)
	scalar depsdev = r(sd)
	gen MNHLTH1_std = ( MNHLTH1 - depmean ) / depsdev

	qui sum ADLHLP1 if age>53 & age<61 [aweight=wtcrnhr]
	scalar depmean = r(mean)
	scalar depsdev = r(sd)
	gen ADLHLP1_std = ( ADLHLP1 - depmean ) / depsdev


	qui sum IADLHP1 if age>53 & age<61 [aweight=wtcrnhr]
	scalar depmean = r(mean)
	scalar depsdev = r(sd)
	gen IADLHP1_std = ( IADLHP1 - depmean ) / depsdev


	qui sum PHYLIMS1 if age>53 & age<61 [aweight=wtcrnhr]
	scalar depmean = r(mean)
	scalar depsdev = r(sd)
	gen PHYLIMS1_std = ( PHYLIMS1 - depmean ) / depsdev

	factor RTHLTH1_std MNHLTH1_std ADLHLP1_std IADLHP1_std PHYLIMS1_std if age>53 & age<61 [aweight=wtcrnhr], pcf factors(1)
	predict H
	
	 	
	drop if H==.
	
// Discretize H
tabstat H if age>53 & age<60 [aweight=wtcrnhr], stat (p50 mean p75 p90)
egen x_P =  pctile(H) if age>53 & age<61 , p(91)
qui sum x_P
scalar x_P1 = r(mean)
gen H_dis = 1 if H>=x_P1 //poor H

egen x_PP =  pctile(H) if age>53 & age<61 , p(54)
qui sum x_PP
scalar x_P2 = r(mean)
replace H_dis = 2 if H>x_P2 & H_dis!= 1 //Fair H

replace H_dis = 3 if H<=x_P2 // good
 tab H_dis [aweight=wtcrnhr] if age>=54 & age<60

 
 drop x_*
 drop H
 rename H_dis H

bysort educ_group: tab H
	
sort ID wave	
	
gen agesq=age^2
gen agecub=age^3
gen H_trans1 = 0 if H==3 & H[_n+1]==3 & ID==ID[_n+1] & wave== wave[_n+1]-1
replace H_trans1 = 1 if H==3 & H[_n+1]!=3 & ID==ID[_n+1] & wave== wave[_n+1]-1 // health went to 1 or 2, so deteriorated.

* now look at probability of any recovery -- any improvement in health
gen H_trans2 = 1 if H==2 & H[_n+1]==3 & ID==ID[_n+1] & wave== wave[_n+1]-1 // went from fair to good
replace H_trans2 = 1 if H==1 & H[_n+1]==3 & ID==ID[_n+1] & wave== wave[_n+1]-1 // from poor to good
replace H_trans2 = 1 if H==1 & H[_n+1]==2 & ID==ID[_n+1] & wave== wave[_n+1]-1 // from poor to fair
replace H_trans2 = 0 if H==2 & (H[_n+1]==2 | H[_n+1]==1) & ID==ID[_n+1] & wave== wave[_n+1]-1
replace H_trans2 = 0 if H==1 & (H[_n+1]==1 ) & ID==ID[_n+1] & wave== wave[_n+1]-1



 
 label var total_wealth "Total Wealth"
 label var H "H"
 
 label define mstatus1 0 "Single" 1 "Married" 
label value marital_status_indic1 mstatus1

 label define health1 1 "H=Poor" 2 "H=Fair"  3 "H=Good"
 label value H health1

  label define ed1 1 "HS or Less" 2 "Some College"  3 "College"
  label value educ_group ed1
  
  label variable educ_group "Education"
  label variable H "Health"

******************************************
* correlations of wealth and H
******************************************
bysort H: tab wealth_group if age>55 & age<61

corr H wealth_group if age>55 & age<61
tab H wealth_group if age>55 & age<61, row nofreq

* note that for this table, need to copy paste into lyx, and need to clean up a bit
//tabout H wealth_group if age>62 & age<67 using "${out_tables}/HRS_H_wealth.tex", cells(row  )  replace style(tex) 

xtile w_group1=total_wealth if age>55 & age<61 & educ_group==1 [aweight=wtcrnhr],n(3)
xtile w_group2=total_wealth if age>55 & age<61 & educ_group==2 [aweight=wtcrnhr],n(3)
xtile w_group3=total_wealth if age>55 & age<61 & educ_group==3 [aweight=wtcrnhr],n(3)


gen w_group=w_group1 if  educ_group==1
replace w_group=w_group2 if  educ_group==2
replace w_group=w_group3 if  educ_group==3

label var w_group "Wealth Tercile"

  label define tercile1 1 "1st" 2 "2nd"  3 "3rd"
  label value w_group tercile1
  
  
* Table 48 left panel - Health and Wealth correlation
table  (educ_group  w_group)   if age>55 & age<61 ,  statistic(fvpercent H)   nformat(%5.1f)  nototals
collect title "Health distribution within wealth terciles, by education, ages 56-60, HRS"
collect export "${out_tables}/HRS_H_wealth.tex", tableonly replace

}


*	Table 53 – Assets statistics 
qui{
clear 
use "${data}/HRS_Analysis.dta"
*keep alive and dead: 1 and 4 are alive and 5 and 6 are dead
keep if iwstatr==1 | iwstatr==4 

* keep Whites
drop if hispanic==1 // drop Hispanics
drop if race==2 // drop Blacks

rename iwendmr month // interview end date month

gen wealth_use = total_wealth - aothrh - atranh 
replace wealth_use=wealth_use/1000
replace wealth_use=0 if wealth_use<0
replace wealth_use=. if wealth_use>1500 

replace total_wealth=total_wealth/1000
replace total_wealth=0 if total_wealth<0
replace total_wealth=. if total_wealth>1500 

* financial wealth (net value of non-housing wealth + IRAs)
gen total_financial_wealth=atotfh+ airah // net value of non-housing financial wealth - excludes any real estate, vehicles or businesses
replace total_financial_wealth =total_financial_wealth /1000 
replace total_financial_wealth=. if total_financial_wealth>1500 


gen educ_group=1 if raedyrs <=12
replace educ_group=2 if raedyrs>=13 & raedyrs<16
replace educ_group=3 if raedyrs>=16 & raedyrs!=.

keep if gender==0  // keep males 0=male, 1=women

sort year

 
 label define mstatus1 0 "Single" 1 "Married" 
label value marital_status_indic1 mstatus1


  label define ed1 1 "HS or Less" 2 "Some College"  3 "College"
  label value educ_group ed1
  
  label variable educ_group "Education"



* assets profiles


gen I_zero_fin_assets=1 if total_financial_wealth<10
replace I_zero_fin_assets=0 if total_financial_wealth>=10 & total_financial_wealth!=.

gen I_zero_tot_assets=1 if total_wealth<10
replace I_zero_tot_assets=0 if total_wealth>=10 & total_wealth!=.

gen I_zero_use=1 if wealth_use<10
replace I_zero_use=0 if wealth_use>=10 & wealth_use!=.


gen total_financial_wealth_nn=total_financial_wealth //financial wealth if non-negligible
replace total_financial_wealth_nn=. if total_financial_wealth<10

gen total_wealth_nn=total_wealth //financial wealth if non-negligible
replace total_wealth_nn=. if total_wealth<10

gen total_wealth_nn_use=wealth_use //financial wealth if non-negligible
replace total_wealth_nn_use=. if wealth_use<10

label var I_zero_fin_assets "% Negligible"
label var  I_zero_use "% Negligible"
label var I_zero_tot_assets "% Negligible"
label var total_financial_wealth_nn "If non-negligible"
label var total_wealth_nn "If non-negligible"
label var total_wealth_nn_use "If non-negligible"

table  (educ_group) if age>=55 & age<61 [aweight=wtcrnhr],  statistic( mean I_zero_use) statistic(p25 total_wealth_nn_use) statistic(median total_wealth_nn_use) statistic(p75 total_wealth_nn_use) nototals nformat(%5.2f)  // weights make it higher

collect title "Assets statistics, ages 55-60, HRS"
collect export "${out_tables}/HRS_wealth_zero_assets_tot.tex", tableonly replace


		
}
	